package com.theexceptions.todo.model;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;

import android.content.*;
import android.database.*;
import android.database.sqlite.*;
import android.util.Log;

/**
 * TODO should this be a static class? A class that assists in all database
 * interactions within the Application. All databases are located locally on the
 * android device's harddrive.
 * 
 * @author JD Reddaway and Justian Meyer
 */
public class DatabaseHelper extends SQLiteOpenHelper implements IPersister {
	static final int DB_VERSION = 1;
	static final String DB_NAME = "tm2_db";

	static final String USERS_TABLE = "Users";
	static final String USERS_ID = "_id";
	static final String USERS_USERNAME = "username";
	static final String USERS_PASSWORD = "password";
	static final String USERS_NAME = "name";
	static final String USERS_EMAIL = "email";

	static final String TASKS_TABLE = "Tasks";
	static final String TASKS_ID = "_id";
	static final String TASKS_NAME = "name";
	static final String TASKS_USERID = "userID";
	static final String TASKS_DATE = "date";
	static final String TASKS_CATEGORY = "category";
	static final String TASKS_DESCRIPTION = "description";
	static final String TASKS_LOCID = "locID";
	static final String TASKS_COMPLETED = "completed";

	static final String CATEGORY_TABLE = "Categories";
	static final String CATEGORY_NAME = "name";
	static final String CATEGORY_USERID = "userID";

	static final String REMINDER_TABLE = "Reminders";
	static final String REMINDER_TIME = "time";
	static final String REMINDER_ID = "_id";
	static final String REMINDER_TASKID = "taskID";

	static final String LOCATIONS_TABLE = "Locations";
	static final String LOCATIONS_ID = "_id";
	static final String LOCATIONS_USERID = "userID";
	static final String LOCATIONS_NAME = "name";
	static final String LOCATIONS_LATITUDE = "lat";
	static final String LOCATIONS_LONGITUDE = "long";

	/**
	 * Constructs a new DatabaseHelper with the given context.
	 * 
	 * @param context
	 */
	public DatabaseHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}

	@Override
	public void init() {
		this.getWritableDatabase().close(); // will call the onCreate() method
											// if it is the first time being
											// opened
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// SQLite datatypes: http://sqlite.org/datatype3.html

		createTables(db);
		addDefaultData(db);

		Log.v("DatabaseHelper.onCreate()", "Database successfully created with default data.");
	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);

		if (!db.isReadOnly()) {
			db.execSQL("PRAGMA foreign_keys=ON;");
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.beginTransaction();
		db.execSQL("DROP TABLE IF EXISTS " + USERS_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + TASKS_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + CATEGORY_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + REMINDER_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + LOCATIONS_TABLE);
		db.setTransactionSuccessful();
		db.endTransaction();
	}

	@Override
	public long insertUser(String username, String password, String name, String email) {
		SQLiteDatabase db = getWritableDatabase();
		long rowID = -1;

		ContentValues values = new ContentValues();
		values.put("username", username);
		values.put("password", password);
		values.put("name", name);
		values.put("email", email);

		rowID = db.insert(USERS_TABLE, null, values); // SQLiteConstraintExceptions
														// cannot be caught;
														// they are caught
														// inside the insert
														// method

		db.close();

		return rowID;
	}

	@Override
	public boolean insertCategory(long userID, String categoryName) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues vals = new ContentValues();
		boolean ret;

		vals.put(CATEGORY_USERID, userID);
		vals.put(CATEGORY_NAME, categoryName);

		ret = -1 != db.insert(CATEGORY_TABLE, null, vals);
		db.close();

		return ret;
	}

	@Override
	public long insertLocation(String name, long userID, long latitude, long longitude) {
		SQLiteDatabase db = getWritableDatabase();
		ContentValues vals = new ContentValues();
		Long rowId;

		vals.put(LOCATIONS_NAME, name);
		vals.put(LOCATIONS_USERID, userID);
		vals.put(LOCATIONS_LATITUDE, latitude);
		vals.put(LOCATIONS_LONGITUDE, longitude);

		rowId = db.insert(LOCATIONS_TABLE, null, vals);

		db.close();
		return rowId;
	}

	@Override
	public long insertTask(long userID, String name, Calendar time, Long locID, Long repeatID,
			String category, String description) {
		/*
		 * JUnit tested
		 * TODO make this return -1 if there was a database error
		 */
		
		SQLiteDatabase db = getWritableDatabase();
		long rowID = -1;

		ContentValues values = new ContentValues();
		values.put(TASKS_NAME, name);
		values.put(TASKS_USERID, userID);
		values.put(TASKS_COMPLETED, false);
		
		if (category != null) {
			values.put(TASKS_CATEGORY, category);
		}
		if (locID != null) {
			values.put(TASKS_LOCID, locID);
		}
		if (description != null) {
			values.put(TASKS_DESCRIPTION, description);
		}
		if (repeatID != null) {
			// TODO put repeatId
		}
		if (time != null) {
			values.put(TASKS_DATE, time.getTimeInMillis());
		}

		rowID = db.insert(TASKS_TABLE, null, values);

		db.close();

		return rowID;
	}

	@Override
	public void removeLocation(long locId) {
		SQLiteDatabase db = this.getWritableDatabase();

		db.delete(LOCATIONS_TABLE, LOCATIONS_ID + "='" + locId + "'", null);
		db.close();
	}

	@Override
	public User retrieveUser(long userID) {
		if (userID < 0) // TODO should we make this a precondition?
			return User.EMPTY_USER;

		// Instantiate database variables
		SQLiteDatabase db = null;
		String[] columns = { "_id", "username", "password", "name", "email" };
		Cursor c = null;

		User user = User.EMPTY_USER;

		// Retrieve cursor with user information from database
		db = getWritableDatabase();
		c = db.query(true, USERS_TABLE, columns, "_id=" + userID, null, null, null, null, null);

		// Move cursor to found user if one exists
		// Return false if no user is found (size == 0)
		if (!c.moveToFirst()) {
			return user;
		}

		// Get user information from cursor
		String username = c.getString(c.getColumnIndex("username"));
		String password = c.getString(c.getColumnIndex("password"));
		String name = c.getString(c.getColumnIndex("name"));
		String email = c.getString(c.getColumnIndex("email"));

		// Create new user object with retrieved values
		user = new User(userID, username, password, name, email);

		Log.i("DatabaseHelper.retrieveUser()", "Retrieved User: " + user.toString());
		c.close(); // close cursor
		db.close(); // close database

		return user;
	}

	@Override
	public User retrieveUser(String username) {
		// Instantiate database variables
		SQLiteDatabase db = getWritableDatabase();
		String[] columns = { "_id", "username", "password", "name", "email" };
		Cursor c = db.query(true, USERS_TABLE, columns, "username='" + username + "'", null, null, null,
				null, null); // Retrieve cursor with user information from
								// database
		User user = User.EMPTY_USER;

		// Move cursor to found user if one exists
		// Return false if no user is found (size == 0)
		if (c.moveToFirst()) {
			// Get user information from cursor
			int userID = c.getInt(c.getColumnIndex("_id"));
			String password = c.getString(c.getColumnIndex("password"));
			String name = c.getString(c.getColumnIndex("name"));
			String email = c.getString(c.getColumnIndex("email"));

			// Create new user object with retrieved values
			user = new User(userID, username, password, name, email);

			Log.v("DatabaseHelper.retrieveUser()", "Retrieved User: " + user.toString());
		}

		c.close(); // close cursor
		db.close(); // close database

		return user;
	}

	@Override
	public List<String> retrieveCategories(long userID) {
		SQLiteDatabase db = getWritableDatabase();
		Cursor results;
		List<String> ret;

		results = db.rawQuery(
				"SELECT " + CATEGORY_NAME + " FROM " + CATEGORY_TABLE +
						" WHERE " + CATEGORY_USERID + "='" + userID + "'" +
						" ORDER BY " + CATEGORY_NAME,
				null
				);

		ret = new ArrayList<String>(results.getCount());

		if (results.moveToFirst()) {
			while (!results.isAfterLast()) {
				ret.add(results.getString(0));
				results.moveToNext();
			}
		}

		results.close();
		db.close();

		return ret;
	}

	/**
	 * Returns a list of the user's current task locations.
	 * 
	 * @param userID
	 * @return list of locations
	 */
	public List<Location> retrieveLocations(long userID) {
		SQLiteDatabase db = getWritableDatabase();

		ArrayList<Location> locations = new ArrayList<Location>();

		String query = String.format("SELECT * FROM %s WHERE %s=%d ORDER BY %s", LOCATIONS_TABLE,
				LOCATIONS_USERID, userID, LOCATIONS_NAME);
		Cursor results = db.rawQuery(query, null);

		if (results.moveToFirst()) {
			while (!results.isAfterLast()) {
				long id = results.getLong(results.getColumnIndex(LOCATIONS_ID));
				String name = results.getString(results.getColumnIndex(LOCATIONS_NAME));
				int lat = results.getInt(results.getColumnIndex(LOCATIONS_LATITUDE));
				int lon = results.getInt(results.getColumnIndex(LOCATIONS_LONGITUDE));

				System.out.println(String.format("[\"%s\"> %d:%d", name, lat, lon));
				
				locations.add(new Location(id, name, lat, lon));

				results.moveToNext();
			}
		}

		results.close();
		db.close();

		return locations;
	}

	/**
	 * Appends filterSQL to the Where clause in the retrieve statement
	 * 
	 * @param userID The user's id of the tasks to retieve.
	 * @param filterSQL The SQL to add to the Where clause of the query. null if
	 *            nothing should be added.
	 * @return A list of tasks depending on userID and filterSQL
	 */
	private List<Task> retrieveTasks(long userID, String filterSQL) {
		// TODO add Repeats to query/task creation
		// TODO add Reminder to query/task

		final String TID = "tID";
		final String TNAME = "tName";
		final String T_USERID = "tUserID";
		final String LOCID = "lID";
		final String LOCNAME = "lName";

		SQLiteDatabase db = getWritableDatabase();
		Cursor results;
		List<Task> ret;

		Calendar time;
		Location loc;

		/* Constructor for task
		public Task(long taskId, String name, Calendar time, Location location, Repeat repeat, Reminder reminder,
				String category, String description) {
		*/

		// not using SELECT * because TASKS and LOCATIONS both have a name
		// column
		results = db.rawQuery(
				"SELECT " +
					TASKS_TABLE + "." + TASKS_ID + " AS " + TID + ", " +
					TASKS_TABLE + "." + TASKS_NAME + " AS " + TNAME + ", " +
					TASKS_TABLE + "." + TASKS_USERID + " AS " + T_USERID + ", " +
					TASKS_DATE + ", " +
					TASKS_LOCID + ", " +
					TASKS_CATEGORY + ", " +
					TASKS_DESCRIPTION + ", " +
					TASKS_COMPLETED + ", " +
					LOCATIONS_TABLE + "." + LOCATIONS_ID + " AS " + LOCID + ", " +
					LOCATIONS_TABLE + "." + LOCATIONS_NAME + " AS " + LOCNAME + ", " +
					LOCATIONS_LATITUDE + ", " +
					LOCATIONS_LONGITUDE + " " +
				"FROM " + TASKS_TABLE + " LEFT OUTER JOIN " + LOCATIONS_TABLE +
					" ON "
						+ TASKS_LOCID + "=" + LOCATIONS_TABLE + "." + LOCATIONS_ID + " AND " +
						T_USERID + "=" + LOCATIONS_TABLE + "." + LOCATIONS_USERID +
				" WHERE " + 
						T_USERID + "='" + userID + "'" + (filterSQL == null ? "" : " AND " + filterSQL) +
				" ORDER BY " + TASKS_DATE + " DESC",
				null
				);

		ret = new ArrayList<Task>(results.getCount());

		if (results.moveToFirst()) {
			while (!results.isAfterLast()) {
				time = new GregorianCalendar();
				
				long locID = results.getLong(results.getColumnIndex(LOCID));
				String locName = results.getString(results.getColumnIndex(LOCNAME));
				int lat = results.getInt(results.getColumnIndex(LOCATIONS_LATITUDE));
				int lon = results.getInt(results.getColumnIndex(LOCATIONS_LONGITUDE));
				
				loc = new Location(locID, locName, lat, lon); // TODO add correct code
				System.out.println(locName + " " + lat + " " + lon);
				
				time.setTimeInMillis(results.getLong(results.getColumnIndex(TASKS_DATE)));

				ret.add(new Task(
						results.getLong(results.getColumnIndex(TID)),
						results.getLong(results.getColumnIndex(T_USERID)),
						results.getString(results.getColumnIndex(TNAME)),
						time,
						loc,
						null,
						null,
						results.getString(results.getColumnIndex(TASKS_CATEGORY)),
						results.getString(results.getColumnIndex(TASKS_DESCRIPTION)),
						results.getInt(results.getColumnIndex(TASKS_COMPLETED)) > 0
						));

				results.moveToNext();
			}
		}

		results.close();
		db.close();

		return ret;
	}

	@Override
	public List<Task> retrieveTasks(long userID) {
		return retrieveTasks(userID, null);
	}

	@Override
	public List<Task> retrieveTasksByCompletion(long userID, boolean isCompleted) {
		int c = (isCompleted) ? 1 : 0;
		return retrieveTasks(userID, TASKS_COMPLETED + "='" + c + "'");
	}

	@Override
	public List<Task> retrieveTasksByDate(long userID, Calendar date) {
		return retrieveTasks(userID, TASKS_DATE + ">='" + date.getTimeInMillis() + "'");
	}

	@Override
	public List<Task> retrieveTasksByCategory(long userID, String category) {
		if (category == null || category.equals("None"))
			return retrieveTasks(userID, TASKS_CATEGORY + " IS NULL");

		return retrieveTasks(userID, TASKS_CATEGORY + "='" + category + "'");
	}

	@Override
	public List<Task> retrieveTasksWithFilters(long userID, List<TaskFilter> filters) {
		String filterSQL;

		if (filters.size() <= 0)
			filterSQL = null;
		else {
			filterSQL = "";
			for (int i = 0; i < filters.size(); i++) {
				filterSQL += " " + filters.get(i).getQuery();

				if (i != filters.size() - 1)
					filterSQL += " AND";
			}
		}

		return retrieveTasks(userID, filterSQL);
	}

	@Override
	public boolean toggleTaskCompleted(Task task) {
		boolean completed = task.toggleCompleted();
		int c = (completed) ? 1 : 0;

		SQLiteDatabase db = null;
		String qry = "UPDATE " + TASKS_TABLE + " SET completed=" + c + " WHERE " + TASKS_ID + "='"
				+ task.getTaskID() + "';";

		try {
			db = getWritableDatabase();
			db.execSQL(qry);
		} finally {
			if (db != null)
				db.close();
		}

		return completed;
	}

	@Override
	public User modifyUser(long userID, String username, String password, String name, String email) {
		SQLiteDatabase db = null;
		String qry = "UPDATE " + USERS_TABLE + " SET username='" + username + "'," + " password='" + password
				+ "'," + " name='" + name + "'," + " email='" + email + "'" + " WHERE _id='" + userID + "';";

		User retrieved = null;

		try {
			db = getWritableDatabase();
			db.execSQL(qry);
			retrieved = retrieveUser(userID);
		} catch (SQLiteConstraintException e) {
			Log.e("DatabaseHelper.modifyUser()", "Username '" + username + "' is not unique.", e);
		} catch (Exception e) {
			Log.e("DatabaseHelper.modifyUser()", "An error occured trying to modify a user.");
		} finally {
			if (db != null)
				db.close();
		}

		return retrieved;
	}

	/**
	 * Adds default data into the database.
	 * 
	 * @param db The database to which to add the default data.
	 */
	private void addDefaultData(SQLiteDatabase db) {
		ContentValues userDefaultVals = new ContentValues();
		ContentValues tsk1Vals = new ContentValues();
		ContentValues catHWVals = new ContentValues();
		ContentValues catSchoolVals = new ContentValues();
		ContentValues catPersonalVals = new ContentValues();
		ContentValues catWorkVals = new ContentValues();

		final long USER1ID = 0;

		catHWVals.put(CATEGORY_NAME, "Homework");
		catHWVals.put(CATEGORY_USERID, USER1ID);

		catSchoolVals.put(CATEGORY_NAME, "School");
		catSchoolVals.put(CATEGORY_USERID, USER1ID);

		catPersonalVals.put(CATEGORY_NAME, "Personal");
		catPersonalVals.put(CATEGORY_USERID, USER1ID);

		catWorkVals.put(CATEGORY_NAME, "Work");
		catWorkVals.put(CATEGORY_USERID, USER1ID);

		userDefaultVals.put(USERS_ID, USER1ID);
		userDefaultVals.put(USERS_USERNAME, "default");
		userDefaultVals.put(USERS_PASSWORD, "password");
		userDefaultVals.put(USERS_NAME, "default name");
		userDefaultVals.put(USERS_EMAIL, "jdreddaway@gatech.edu");

		tsk1Vals.put(TASKS_NAME, "Finish TODO project");
		tsk1Vals.put(TASKS_DESCRIPTION, "Need to add tasks correctly.");
		tsk1Vals.put(TASKS_CATEGORY, "Homework");
		tsk1Vals.put(TASKS_USERID, USER1ID);
		tsk1Vals.put(TASKS_COMPLETED, true);

		db.beginTransaction();
		db.insert(USERS_TABLE, null, userDefaultVals);
		db.insert(CATEGORY_TABLE, null, catHWVals);
		db.insert(CATEGORY_TABLE, null, catSchoolVals);
		db.insert(CATEGORY_TABLE, null, catPersonalVals);
		db.insert(CATEGORY_TABLE, null, catWorkVals);
		db.insert(TASKS_TABLE, null, tsk1Vals);

		db.setTransactionSuccessful();
		db.endTransaction();
		// db.close(); DO NOT CLOSE DATABSE in onCreate()
	}

	private void dropTables(SQLiteDatabase db) {
		db.beginTransaction();
		try {
			db.execSQL("DROP TABlE IF EXISTS " + TASKS_TABLE);
			db.execSQL("DROP TABlE IF EXISTS " + CATEGORY_TABLE);
			db.execSQL("DROP TABlE IF EXISTS " + REMINDER_TABLE);
			db.execSQL("DROP TABlE IF EXISTS " + LOCATIONS_TABLE);
			db.execSQL("DROP TABlE IF EXISTS " + USERS_TABLE);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}

	/**
	 * Creates tables in the database.
	 * 
	 * @param db The database object to use to create the tables.
	 */
	private void createTables(SQLiteDatabase db) {
		db.beginTransaction(); // I do not have in a try block because database
								// errors are fatal errors
		db.execSQL("CREATE TABLE " + USERS_TABLE + " (" +
				USERS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + USERS_USERNAME
				+ " VARCHAR(35) NOT NULL UNIQUE, " +
				USERS_PASSWORD + " VARCHAR(50) NOT NULL, " + USERS_NAME + " VARCHAR(70), " + USERS_EMAIL
				+ " VARCHAR(128)" + ");");

		// http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx
		db.execSQL("CREATE TABLE " + CATEGORY_TABLE + " (" +
				CATEGORY_NAME + " VARCHAR(30) NOT NULL, " +
				CATEGORY_USERID + " INTEGER NOT NULL, " +
				"PRIMARY KEY(" + CATEGORY_NAME + ", " + CATEGORY_USERID + "), " +
				"FOREIGN KEY(" + CATEGORY_USERID + ") REFERENCES " + USERS_TABLE + "(" + USERS_ID + ")" +
				")");
		db.execSQL("CREATE TABLE " + REMINDER_TABLE + " (" +
				REMINDER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
				REMINDER_TIME + " INTEGER NOT NULL, " +
				REMINDER_TASKID + " INTEGER NOT NULL, " +
				"FOREIGN KEY(" + REMINDER_TASKID + ") REFERENCES " + TASKS_TABLE + "(" + TASKS_ID + ")" +
				")");
		db.execSQL("CREATE TABLE " + LOCATIONS_TABLE + " (" +
				LOCATIONS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
				LOCATIONS_USERID + " INTEGER, " +
				LOCATIONS_NAME + " VARCHAR(50), " +
				LOCATIONS_LATITUDE + " REAL NOT NULL, " +
				LOCATIONS_LONGITUDE + " REAL NOT NULL, " +
				"FOREIGN KEY( " + LOCATIONS_USERID + ") REFERENCES " + USERS_TABLE + "(" + USERS_ID + ")" +
				")");
		db.execSQL("CREATE TABLE " + TASKS_TABLE + " (" +
				TASKS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
				TASKS_NAME + " VARCHAR(50), " +
				TASKS_USERID + " INTEGER NOT NULL, " +
				TASKS_DATE + " INTEGER, " +
				TASKS_CATEGORY + " VARCHAR(30), " +
				TASKS_DESCRIPTION + " VARCHAR(255), "
				+ // was a blob
				TASKS_LOCID + " INTEGER, " +
				TASKS_COMPLETED + " BOOLEAN NOT NULL, " +
				"FOREIGN KEY(" + TASKS_USERID + ") REFERENCES " + USERS_TABLE + "(" + USERS_ID + "), " +
				"FOREIGN KEY(" + TASKS_USERID + ", " + TASKS_CATEGORY + ") REFERENCES " + CATEGORY_TABLE
				+ "(" + CATEGORY_USERID + ", " + CATEGORY_NAME + "), " + // problem
				"FOREIGN KEY(" + TASKS_LOCID + ") REFERENCES " + LOCATIONS_TABLE + "(" + LOCATIONS_ID + ")" + // problem
				")");

		db.setTransactionSuccessful();
		db.endTransaction();
	}

	@Override
	public void clearTables() {
		SQLiteDatabase db = this.getWritableDatabase();

		dropTables(db);
		createTables(db);
		addDefaultData(db);

		db.close();
	}
}
